﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="Client.cs" company="BerlinSoft">
//   Wernfried Schwenkner
// </copyright>
// <summary>
//   Defines the Client type.
// </summary>
// --------------------------------------------------------------------------------------------------------------------

using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using System.Windows.Forms;
using SenseTimeTracker.Misc;
using SenseTimeTracker.Properties;
using StedySoft.SenseSDK;
using StedySoft.SenseSDK.DrawingCE;

namespace SenseTimeTracker.BusinessLayer
{
	public class Client : IColumnSortable
	{
		public const string IdColumnName = "clientId";
		public const string NameColumnName = "clientName";
		public const string TypeColumnName = "clientType";
		public const string TableName = "Clients";

		private Guid id = Guid.NewGuid();
		private string name = string.Empty;
		private string type = string.Empty;

		public string Number
		{
			get { return string.Empty; }
		}

		public DateTime Date
		{
			get { return DateTime.MinValue; }
		}

		public string Description
		{
			get { return string.Empty; }
		}

		public TimeSpan Duration
		{
			get { return TimeSpan.Zero; }
		}
		public string Type
		{
			get { return type; }
			set { type = value; }
		}

		public string Name
		{
			get { return name; }
			set { name = value; }
		}

		public Guid Id
		{
			get { return id; }
			set { id = value; }
		}

		public static List<Client> Clients
		{
			get
			{
				List<Client> clients = new List<Client>();
				SqlCeCommand command = new SqlCeCommand(
						"SELECT " +
						IdColumnName + ", " +
						NameColumnName + ", " +
						TypeColumnName +
						" FROM " + TableName,
						DatabaseManager.DbConnection);
				bool shallOpen = DatabaseManager.DbConnection.State != System.Data.ConnectionState.Open;
				if (shallOpen)
				{
					DatabaseManager.DbConnection.Open();
				}

				SqlCeDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					clients.Add(new Client
					{
						Id = (Guid)reader[IdColumnName],
						Name = reader[NameColumnName].ToString(),
						Type = reader[TypeColumnName].ToString(),
					});
				}

				if (shallOpen)
				{
					DatabaseManager.DbConnection.Close();
				}

				return clients;
			}
		}

		public static Client GetClient(Guid id)
		{
			Client client = null;
			SqlCeCommand command = new SqlCeCommand(
					"SELECT " +
					NameColumnName + ", " +
					TypeColumnName +
					" FROM " + TableName +
					" WHERE " + IdColumnName + "=@" + IdColumnName,
					DatabaseManager.DbConnection);
			command.Parameters.Add(IdColumnName, id);
			DatabaseManager.DbConnection.Open();
			SqlCeDataReader reader = command.ExecuteReader();
			if (reader.Read())
			{
				client = new Client
				{
					Id = id,
					Name = reader[NameColumnName].ToString(),
					Type = reader[TypeColumnName].ToString(),
				};
			}

			DatabaseManager.DbConnection.Close();
			return client;
		}

		public void Insert()
		{
			SqlCeCommand command = new SqlCeCommand(
				"INSERT INTO " + TableName + " (" +
				IdColumnName + ", " +
				NameColumnName + ", " +
				TypeColumnName +
				") VALUES (" +
				"@" + IdColumnName + ", " +
				"@" + NameColumnName + ", " +
				"@" + TypeColumnName + ")",
				DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, id);
			command.Parameters.Add(NameColumnName, name);
			command.Parameters.Add(TypeColumnName, type);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}

		public void Update()
		{
			SqlCeCommand command = new SqlCeCommand(
				"UPDATE " + TableName + " SET " +
				NameColumnName + "=@" + NameColumnName + ", " +
				TypeColumnName + "=@" + TypeColumnName + " " +
				"WHERE " + IdColumnName + "=@" + IdColumnName,
				DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, id);
			command.Parameters.Add(NameColumnName, name);
			command.Parameters.Add(TypeColumnName, type);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}

		/// <summary>
		/// Deletes a client record
		/// </summary>
		/// <param name="clientId">Client id to be deleted</param>
		public static void Delete(Guid clientId)
		{
			SqlCeCommand command = new SqlCeCommand(
				"DELETE FROM " + TableName + " " +
				"WHERE " + IdColumnName + "=@" + IdColumnName,
				DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, clientId);
			try
			{
				DatabaseManager.DbConnection.Open();
				command.ExecuteNonQuery();
			}
			catch
			{
				if (SenseAPIs.SenseMessageBox.Show(Resources.DialogMessageRemoveProjectTasksAndCards, Resources.DialogHeaderRemoveClient, SenseMessageBoxButtons.YesNo) == DialogResult.Yes)
				{
					command.CommandText =
						"DELETE FROM " + TimeCard.TableName + " " +
						"WHERE " + TimeCard.TaskIdColumnName  + " " +
						"IN (SELECT " + Task.IdColumnName + " " +
						"FROM " + Task.TableName + " " +
						"JOIN " + Project.TableName + " " +
						"ON " + Project.TableName + "." + Project.IdColumnName + "=" + Task.TableName + "." + Task.ProjectIdColumnName + " " +
						"WHERE " + Project.TableName + "." + Project.ClientIdColumnName + "=@" + Project.ClientIdColumnName + ")";
					command.ExecuteNonQuery();

					command.CommandText =
						"DELETE FROM " + Task.TableName + " " +
						"WHERE " + Task.ProjectIdColumnName + " " +
						"IN (SELECT " + Project.IdColumnName + " " +
						"FROM " + Project.TableName + " " +
						"WHERE " + Project.TableName + "." + Project.ClientIdColumnName + "=@" + Project.ClientIdColumnName + ")";
					command.ExecuteNonQuery();

					command.CommandText =
						"DELETE FROM " + Project.TableName + " " +
						"WHERE " + Project.ClientIdColumnName + "=@" + Project.ClientIdColumnName;
					command.ExecuteNonQuery();

					command.CommandText =
						"DELETE FROM " + TableName + " " +
						"WHERE " + IdColumnName + "=@" + IdColumnName;
					command.ExecuteNonQuery();
				}
			}
			finally
			{
				DatabaseManager.DbConnection.Close();
			}
		}
	}
}
